package com.hnkywl.sts.dao.jhgl;

import com.hnkywl.sts.dao.base.RegionDao;
import com.hnkywl.sts.entity.jhgl.Cgwzjh;
import com.hnkywl.sts.service.sys.YgService;
import com.hnkywl.sts.util.StringFormat;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.transform.Transformers;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 * User: ZHUCHG
 * Date: 13-4-2
 * Time: 下午9:24
 * To change this template use File | Settings | File Templates.
 */
public class WzjhDao<T extends Cgwzjh> extends SimpleHibernateTemplate<T, Long> {
    /**
     * @param jhnf     计划年份
     * @param jhyf     计划月份
     * @param jhbh     计划编号
     * @param sbgsId   申报部门ID
     * @param gysId    供应商ID
     * @param zt       审批状态
     * @param tcgsId   统筹公司ID
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Page<Map> findList(String jhnf, String jhyf, String jhbh, Long sbgsId, Long gysId, String zt, Long tcgsId, String wzbm, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        List<Object> param = new ArrayList<Object>();
        List<String> conditions = new ArrayList<String>();
        if (StringUtils.isNotEmpty(jhnf)) {
            conditions.add(" c.xqnf=? ");
            param.add(jhnf);
        }
        if (StringUtils.isNotEmpty(jhyf)) {
            conditions.add(" c.xqyf=? ");
            param.add(jhyf);
        }
        if (StringUtils.isNotEmpty(jhbh)) {
            conditions.add(" c.jhbh=? ");
            param.add(jhbh);
        }
        if (sbgsId != null) {
            conditions.add(" c.sbgs.id=? ");
            param.add(sbgsId);
        }
        if (gysId != null) {
            conditions.add(" g.id=? ");
            param.add(gysId);
        }
        if (StringUtils.isNotEmpty(zt)) {
            conditions.add(" c.zt=? ");
            param.add(zt);
        }
        if (StringUtils.isNotEmpty(wzbm)) {
            conditions.add(" (g1.code like ? or  g2.code like ? )");
            param.add("%" + wzbm.trim() + "%");
            param.add("%" + wzbm.trim() + "%");
        }
        if ("Tpwzjh".equals(entityClass.getSimpleName()))
            conditions.add(" lower(c.type)='tpwzjh' ");
        if ("Zgwzjh".equals(entityClass.getSimpleName()))
            conditions.add(" lower(c.type)='zgwzjh' ");

        String sqlCounter = "select count(distinct c.id) ";
        String sqlColumn = "select distinct c.type \"clazz\",c.lc_id as \"processInstanceId\",c.id \"id\",c.jhbh \"jhbh\",g.qc \"gys_qc\",k.qc \"kh_qc\",d.qc \"dbf_qc\",sbgs.jc \"sbgs_jc\"," +
                "sbbm.qc \"sbbm_qc\",c.sbsl \"sbsl\",c.bz \"bz\",y.xm \"zdr_xm\",c.zdsj \"zdsj\",c.zt \"zt\",c.date_created \"date_created\"";
        String sqlJoinTable = " from t_biz_cgwzjh c " +
                " left join t_sys_gsjg g on g.type='qyjg' and c.gys_id=g.id and g.deleted=0 " +
                " left join t_sys_gsjg k on k.type='qyjg' and c.kh_id=k.id and k.deleted=0 " +
                " left join t_sys_gsjg d on d.type='qyjg' and c.dbf_id=d.id and d.deleted=0 " +
                " left join t_sys_gsjg sbgs on sbgs.type='nbgs' and c.sbgs_id=sbgs.id and sbgs.deleted=0 " +
                " left join t_sys_gsjg sbbm on c.sbbm_id=sbbm.id and sbbm.deleted=0 " +
                " left join t_sys_yg y on c.createuser_id=y.id and y.deleted=0 " +
                " left join t_biz_xy x on c.xy_id=x.id and x.deleted=0 ";
        sqlJoinTable += " left join t_biz_tpwzjhmx mx1 on c.id = mx1.tpwzjh_id and mx1.deleted=0 ";
        sqlJoinTable += " left join t_biz_zgwzjhmx mx2 on c.id = mx2.zgwzjh_id and mx2.deleted=0 ";
        sqlJoinTable += " left join t_sys_gg g1 on g1.id = mx1.wzgg_id and g1.deleted = 0 ";
        sqlJoinTable += "  left join t_sys_gg g2 on g2.id = mx2.wzgg_id and g2.deleted = 0 ";
        String dataRegion = RegionDao.createSQLDataRegion(null, "c");
        //添加统配查询范围
        dataRegion = createTpRegion(dataRegion,"c");
        String sqlCondition = " where c.deleted=0 "
                + (StringUtils.isEmpty(dataRegion) ? "" : "and " + dataRegion)
                + (conditions.isEmpty() ? "" : " and " + StringUtils.join(conditions, " and "));
        String sqlOder = " order by c.date_created desc";
        BigDecimal total = (BigDecimal) createSQLQuery(sqlCounter + sqlJoinTable + sqlCondition, param.toArray()).uniqueResult();
        if (total.intValue() > 0) {
            page.setTotalCount(total.intValue());
            List<Map> list = createSQLQuery(sqlColumn + sqlJoinTable + sqlCondition + sqlOder, param.toArray()).setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setTotalCount(0);
            page.setResult(new ArrayList<Map>());
        }
        return page;
    }

    private String createTpRegion(String dataRegion,String alias) {
        String region = " ({0} or {1}.TCBM_ID = {2}) ";
        return StringFormat.format(region,dataRegion,alias,YgService.getCurrentYgGsId().toString());
    }

    /**
     *首页显示物资计划总数
     * @param xqnf
     * @param xqyf
     * @param zt
     * @return
     */
    public String findWzjhSum(String xqnf, String xqyf, String zt) {
        String dataRegion = RegionDao.createSQLDataRegion(null, "c");
        String wzjhSql = "select count(1) from t_biz_cgwzjh c left join t_sys_yg y on c.createuser_id=y.id and y.deleted=0 " +
                "where c.xqnf = ? and c.xqyf = ? and c.zt = ? and c.deleted= 0 "
                + (StringUtils.isEmpty(dataRegion) ? "" : "and " + dataRegion);
        BigDecimal total = (BigDecimal) createSQLQuery(wzjhSql,xqnf,xqyf,zt).uniqueResult();
        return total.toString();
    }

}
